import datetime
import pytz
import json
import operator
from django.core import serializers
import os
import xlrd
import time
import openpyxl
import xlsxwriter
from django.http import HttpResponse, StreamingHttpResponse

from bookapp.models import Author


def download_template(request, filename):
    date_p = datetime.datetime.now().date()
    str_p = str(date_p)
    fp = open('D:\\workspace\\pycharmworkspace\\djangoProject\\static\\download\\' + filename, 'rb')
    response = StreamingHttpResponse(fp)
    # response = FileResponse(fp)
    response['Content-Type'] = 'application/octet-stream'
    filename = filename.split('.')
    filename = filename[0] + '_' + str_p + '.xlsx'
    response['Content-Disposition'] = 'attachment;filename="%s"' % filename
    return response
    fp.close()


# 上传
def upload(request):
    if request.method == 'POST':
        file_obj = request.FILES.get('file')
        current_time = str(time.time())
        f = open(os.path.join('D:\\workspace\\pycharmworkspace\\djangoProject\\static', 'pic', current_time + file_obj.name), 'wb')
        print(file_obj, type(file_obj))
        for chunk in file_obj.chunks():
            f.write(chunk)  ##数据流写入服务端本机excel文件中
        f.close()
        book = xlrd.open_workbook(
            os.path.join('D:\\workspace\\pycharmworkspace\\djangoProject\\static', 'pic', current_time + file_obj.name))  ##打开服务端excel文件
        sheet1 = book.sheets()[0]
        result = []
        for i in range(1, sheet1.nrows):
            tmp = [sheet1.cell(i, 0).value, sheet1.cell(i, 1).value]
            result.append(tmp)
        for i in range(0, len(result) - 1):  ##判断excel中是否有重复IP  作者姓名
            for j in range(i + 1, len(result)):
                if (result[i][0] == result[j][0]):
                    response = {}
                    response['status'] = 'dumplicate'
                    response['host_ip'] = result[i][0]
                    json_data = json.dumps(response)
                    return HttpResponse(json_data)

        host_list = Author.objects.all()
        for i in range(0, len(result)):  # 判断excel中IP信息是否已存在数据库中 作者信息
            for var in host_list:
                if result[i][0].strip() == var.name.strip():
                    response = {}
                    response['status'] = 'exists'
                    response['name'] = result[i][0]
                    json_data = json.dumps(response)
                    return HttpResponse(json_data)
        for i in range(0, len(result)):  ##excel中数据写入数据库
            hostlistadd = Author(name=result[i][0], age=result[i][1])
            hostlistadd.save()
        response = {}
        response['status'] = 'success'
        json_data = json.dumps(response)
        return HttpResponse(json_data)


# 下载
def download(request):
    f = xlsxwriter.Workbook('D:\\workspace\\pycharmworkspace\\djangoProject\\static\\download\\hostmodel.xlsx')
    bold_head = f.add_format({
        'bold': True,  # 字体加粗
        'border': 1,  # 单元格边框宽度
        'align': 'left',  # 水平对齐方式
        'valign': 'vcenter',  # 垂直对齐方式
        'fg_color': '#48D1CC',  # 单元格背景颜色
        'text_wrap': True,  # 是否自动换行
    })

    worksheet1 = f.add_worksheet('服务器模板')
    data = ['作者名称', '作者年龄']
    worksheet1.write_row("A1", data, bold_head)
    result = []
    tmp = []
    host_list = Author.objects.all()
    for var in host_list:
        tmp = []
        tmp.append(var.name)
        tmp.append(var.age)
        result.append(tmp)
    for i in range(len(result)):
        worksheet1.write(i + 1, 0, result[i][0])
        worksheet1.write(i + 1, 1, result[i][1])

    f.close()
    result = download_template(request, 'hostmodel.xlsx')
    return result
